package gentlesik.mysql.spatial;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.omg.PortableServer.POA;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.CoordinateSequence;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.LinearRing;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;
import com.vividsolutions.jts.geom.impl.CoordinateArraySequenceFactory;
import com.vividsolutions.jts.io.ByteOrderValues;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKBReader;
import com.vividsolutions.jts.io.WKBWriter;

public class JTSTester {
	protected static final int SRID_LENGTH = 4;
	private static final byte XDR = 0;	// big endian
	private static final byte NDR = 1;	// little endian

	private DataSource dataSource;
	WKBReader wkbReader = new WKBReader();
	WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.LITTLE_ENDIAN);

	public static void main(String[] args) {
		BasicDataSource basicDataSource = new BasicDataSource();
		basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
		basicDataSource.setUrl("jdbc:mysql://localhost:3306");
		basicDataSource.setUsername("root");
		basicDataSource.setPassword("ancora5155");
		basicDataSource.setDefaultCatalog("gentlesik");
		
		JTSTester tester = new JTSTester();
		tester.setDataSource(basicDataSource);

		//tester.testInsert();
		
		tester.testSelect();
	}

	private void testInsert() {
		Bean bean = this.getSampleBean();
		
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = this.getDataSource().getConnection();
			String query = "insert into Test (name, location, line, boundary, description) values (?, ?, ?, ?, ?)";
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, bean.getName());
			pstmt.setBytes(2, geometryToMysqlBinary(bean.getLocation()));
			pstmt.setBytes(3, geometryToMysqlBinary(bean.getLine()));
			pstmt.setBytes(4, geometryToMysqlBinary(bean.getBoundary()));
			pstmt.setString(5, bean.getDescription());
			pstmt.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public byte[] geometryToMysqlBinary(Geometry geometry) {
		byte[] wkb = wkbWriter.write(geometry);
		byte[] mysqlBinary = new byte[wkb.length + SRID_LENGTH];
		
		int srid = geometry.getSRID();
		if (isBigEndian(wkb[0])) {
			mysqlBinary[0] = (byte) (srid >> 24);
			mysqlBinary[1] = (byte) (srid >> 16);
			mysqlBinary[2] = (byte) (srid >> 8);
			mysqlBinary[3] = (byte) srid;
		} else {
			mysqlBinary[3] = (byte) (srid >> 24);
			mysqlBinary[2] = (byte) (srid >> 16);
			mysqlBinary[1] = (byte) (srid >> 8);
			mysqlBinary[0] = (byte) srid;
		}
		
		System.arraycopy(wkb, 0, mysqlBinary, SRID_LENGTH, wkb.length);
		return mysqlBinary;
	}

	private boolean isBigEndian(byte flag) {
		if (flag == XDR) return true;
		return false;
	}
	
	private Bean getSampleBean() {
		// location
		GeometryFactory geometryFactory = new GeometryFactory();
		Coordinate coordinate = new Coordinate(8, 9);
		Point location = geometryFactory.createPoint(coordinate);
		
		// line
		Coordinate[] coordinates = new Coordinate[10];
		for (int i = 0; i < coordinates.length; i++) {
			coordinates[i] = new Coordinate(i, i);
		}
		LineString line = geometryFactory.createLineString(coordinates);

		// boundary
		Coordinate[] coordinates2 = new Coordinate[5];
		coordinates2[0] = new Coordinate(0, 0);
		coordinates2[1] = new Coordinate(5, 0);
		coordinates2[2] = new Coordinate(5, 5);
		coordinates2[3] = new Coordinate(0, 5);
		coordinates2[4] = new Coordinate(0, 0);
		CoordinateArraySequenceFactory factory = CoordinateArraySequenceFactory.instance();
		CoordinateSequence coordinateSequence = factory.create(coordinates2);
		LinearRing shell = new LinearRing(coordinateSequence, geometryFactory);
		Polygon boundary = geometryFactory.createPolygon(shell, null);
		
		Bean bean = new Bean();
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMdd-HHmmss");
		bean.setName(sf.format(new Date()));
		bean.setLocation(location);
		bean.setLine(line);
		bean.setBoundary(boundary);
		bean.setDescription("description5");
		return bean;
	}
	
	public void testSelect() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = this.getDataSource().getConnection();
			String query = "select * from Test";
			pstmt = con.prepareStatement(query);
			rs = pstmt.executeQuery();
			
			Bean result = null;
			while (rs.next()) {
				result = parseBean(rs);
				System.out.println(result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) rs.close();
				if (pstmt != null) pstmt.close();
				if (con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private Bean parseBean(ResultSet rs) {
		Bean bean = new Bean();
		try {
			String name = rs.getString("NAME");
			bean.setName(name);
			
			byte[] mysqlBinary = null;
			mysqlBinary = rs.getBytes("LOCATION"); 
			bean.setLocation(parsePoint(mysqlBinary));
			
			mysqlBinary = rs.getBytes("LINE"); 
			bean.setLine(parseLineString(mysqlBinary));
			
			mysqlBinary = rs.getBytes("BOUNDARY"); 
			bean.setBoundary(parsePolygon(mysqlBinary));
			
			String description = rs.getString("description");
			bean.setDescription(description);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return bean;
	}
	
	private Point parsePoint(byte[] mysqlBinary) {
		int srid = getSrid(mysqlBinary);
		
		byte[] wkb = new byte[mysqlBinary.length - 4];
		System.arraycopy(mysqlBinary, 4, wkb, 0, wkb.length);
		Geometry point = null;
		try {
			point = wkbReader.read(wkb);
			point.setSRID(srid);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return (Point) point;
	}

	private LineString parseLineString(byte[] mysqlBinary) {
		int srid = getSrid(mysqlBinary);
		
		byte[] wkb = new byte[mysqlBinary.length - 4];
		System.arraycopy(mysqlBinary, 4, wkb, 0, wkb.length);
		Geometry line = null;
		try {
			line = wkbReader.read(wkb);
			line.setSRID(srid);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return (LineString) line;
	}
	
	private Polygon parsePolygon(byte[] mysqlBinary) {
		int srid = getSrid(mysqlBinary);
		
		byte[] wkb = new byte[mysqlBinary.length - 4];
		System.arraycopy(mysqlBinary, 4, wkb, 0, wkb.length);
		Geometry polygon = null;
		try {
			polygon = wkbReader.read(wkb);
			polygon.setSRID(srid);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return (Polygon) polygon;
	}
	
	private Geometry parseGeometry(byte[] mysqlBinary) {
		int srid = getSrid(mysqlBinary);
		
		byte[] wkb = new byte[mysqlBinary.length - 4];
		System.arraycopy(mysqlBinary, 4, wkb, 0, wkb.length);
		Geometry geometry = null;
		try {
			geometry = wkbReader.read(wkb);
			geometry.setSRID(srid);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return geometry;		
	}
	
	protected int getSrid(byte[] mysqlBinary) {
		if (isBigEndian(mysqlBinary[SRID_LENGTH])) {
			return mysqlBinary[0] << 24 | (mysqlBinary[1] & 0xff) << 16 | (mysqlBinary[2] & 0xff) << 8 | (mysqlBinary[3] & 0xff);
		} else {
			return mysqlBinary[3] << 24 | (mysqlBinary[2] & 0xff) << 16 | (mysqlBinary[1] & 0xff) << 8 | (mysqlBinary[0] & 0xff);
		}
	}
	
	public DataSource getDataSource() {
		return dataSource;
	}
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public class Bean {
		String name;
		Point location;
		LineString line;
		Polygon boundary;
		String description;
		
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public Point getLocation() {
			return location;
		}
		public void setLocation(Point location) {
			this.location = location;
		}
		public LineString getLine() {
			return line;
		}
		public void setLine(LineString line) {
			this.line = line;
		}
		public Polygon getBoundary() {
			return boundary;
		}
		public void setBoundary(Polygon boundary) {
			this.boundary = boundary;
		}
		public String getDescription() {
			return description;
		}
		public void setDescription(String description) {
			this.description = description;
		}
		
		@Override
		public String toString() {
			return "Result [name=" + name + ", location=" + location + ", line=" + line + ", boundary=" + boundary + ", description=" + description + "]";
		}
	}
	
}
